package implicits

import java.io.{File, FileInputStream}
import java.text.SimpleDateFormat
import org.apache.commons.io.FileUtils
import org.apache.commons.lang3.StringUtils
import org.apache.poi.ss.usermodel.{Cell, CellType, DateUtil}
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import scala.collection.JavaConverters._
import implicits.Implicits._

/** Created by Administrator on 2019/11/11
  */

trait MyXlxsTool {

  implicit class MyXlxsFile(file: File) {

    def getNumericValue(cell: Cell) = {
      if (DateUtil.isCellDateFormatted(cell)) {
        val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
        dateFormat.format(cell.getDateCellValue)
      } else {
        val doubleValue = cell.getNumericCellValue
        if (doubleValue == doubleValue.toInt) {
          doubleValue.toInt.toString
        } else doubleValue.toString
      }
    }

    def xlsxLines(sheetIndex: Int = 0) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val lines = (0 to xssfSheet.getLastRowNum).toList
        .filter { i =>
          xssfSheet.getRow(i) != null
        }
        .map { i =>
          val xssfRow = xssfSheet.getRow(i)
          val firstRow = xssfSheet.getRow(0)
          (0 until firstRow.getLastCellNum).map { j =>
            val cell = xssfRow.getCell(j)
            val value = if (cell == null) {
              ""
            } else {
              cell.getCellType match {
                case CellType.STRING =>
                  cell.getStringCellValue
                case CellType.NUMERIC =>
                  getNumericValue(cell)
                case CellType.FORMULA =>
                  cell.getCachedFormulaResultType match {
                    case CellType.NUMERIC =>
                      getNumericValue(cell)
                    case CellType.STRING =>
                      cell.getStringCellValue
                  }
                case _ =>
                  ""
              }
            }
            value.trim
          }.toList
        }
      xssfWorkbook.close()
      lines.filter(x => x.exists(y => StringUtils.isNotBlank(y)))
    }

    def xlsxLinesWithColors(sheetIndex: Int = 0) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val lines = (0 to xssfSheet.getLastRowNum).toList.map { i =>
        val xssfRow = xssfSheet.getRow(i)
        val firstRow = xssfSheet.getRow(0)
        (0 until firstRow.getLastCellNum).map { j =>
          val cell = xssfRow.getCell(j)
          val color = if (cell == null) {
            ""
          } else {
            if (cell.getCellStyle.getFillForegroundColorColor == null) {
              ""
            } else {
              cell.getCellStyle.getFillForegroundColorColor.getARGBHex
            }
          }
          val value = if (cell == null) {
            ""
          } else {
            cell.getCellType match {
              case CellType.STRING =>
                cell.getStringCellValue
              case CellType.NUMERIC =>
                if (DateUtil.isCellDateFormatted(cell)) {
                  val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
                  dateFormat.format(cell.getDateCellValue)
                } else {
                  val doubleValue = cell.getNumericCellValue
                  if (doubleValue == doubleValue.toInt) {
                    doubleValue.toInt.toString
                  } else doubleValue.toString
                }
              case _ =>
                ""
            }
          }
          (value.trim, color)
        }.toList
      }
      xssfWorkbook.close()
      lines.filter(x => x.exists(y => StringUtils.isNotBlank(y._1)))
    }

    def xlsx2Txt(txtFile: File) = {
      val lines = file.xlsxLines()
      lines.toFile(txtFile)
    }

  }

}
